package priv.jesse.mall.dao;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import priv.jesse.mall.entity.Product;
import priv.jesse.mall.entity.Shopcart;

import javax.transaction.Transactional;
import java.util.Date;
import java.util.List;

public interface ProductDao extends JpaRepository<Product, Integer> {
    /**
     * 通过二级分类查找商品列表
     *
     * @param csid
     * @param pageable
     * @return
     */
    List<Product> findByCsid(int csid, Pageable pageable);

    List<Product> findByCsidIn(List<Integer> csids,Pageable pageable);

    /**
     * 通过标题搜索商品
     *
     * @param keyword
     * @param pageable
     * @return
     */
    List<Product> findByTitleIsLike(String keyword, Pageable pageable);

    /**
     * 查找某个日期之后上架的商品
     * @param date
     * @param pageable
     * @return
     */
    List<Product> findByPdateAfter(Date date, Pageable pageable);

    /**
     * 查找热门商品
     * @param isHot
     * @param pageable
     * @return
     */
    List<Product> findByIsHot(int isHot,Pageable pageable);



    /**
     * 查询最新轮播商品
     * @param
     * @return
     */
    @Query(value = "SELECT * FROM  product",nativeQuery = true)
    List<Product> findLunboProduct();

    /**
     * 查询最新商品，最近上新的24个商品
     * @param pageable
     * @return
     */
    @Query(value = "SELECT * FROM (SELECT  * FROM product ORDER BY pdate DESC limit 0,24) a /*#pageable*/",nativeQuery = true)
    List<Product> findNew(Pageable pageable);

    /**
     * 查询各端热门
     * @param
     * @return
     */
    @Query(value = "select p.* from product p WHERE p.is_hot=2 and (p.csid = ?1 or p.csid1=?2) order by pdate desc ",nativeQuery = true)
    List<Product> findCengjihotx(int type1,int type2);

    /**
     * 查询各端
     * @param
     * @return
     */
    @Query(value = "select * from product WHERE csid=?1 or csid1=?2 order by ?3",nativeQuery = true)
    List<Product> findCengjiHot(int cengji1,int cengji2,String orderDir);


    /**
     * 查询一级层级的所有商品
     * @param id
     * @return
     */
    @Query(value = "select p.* from product p LEFT JOIN classification c ON p.csid=c.id WHERE c.parent_id = :id or c.id = :id and p.is_hot>=:hot order by :orderDir",nativeQuery = true)
    List<Product> findAllByType(@Param("id") Integer id,@Param("hot") Integer hot,@Param("orderDir") String orderDir);

    /**
     * 加入购物车
     * @param productId
     * @return
     */
    @Modifying
    @Transactional
    @Query(value = "insert into shopcart (user_id,pro_id,buy_count) values (?1,?2,?3)",nativeQuery = true)
    void addCart(int useid,int productId,int prodCount);


    /**
     * 加载购物车数量
     * @param
     * @return
     */
    @Query(value = "select count(1) from shopcart where user_id=?1",nativeQuery = true)
    int loadShopcount(int useid);




}
